# -*- coding: utf-8 -*-

import time
import os
from datetime import datetime, timedelta
#os.environ['PATH'] ="C:/Oracle/db/bin" + os.pathsep +os.environ['PATH'] 
os.environ['PATH'] ="C:/Oracle/Client/bin" + os.pathsep +os.environ['PATH']
os.environ['NLS_LANG'] = "FRENCH_CANADA.UTF8"
import cx_Oracle
import threading
class CommunicationBaseDonnees(object):
    def __init__(self):
        self.Erreur = ""
        self.Curseur = None
        self.Connection = None
        self.ListeDeclencheur = []
        self.Dict_Invervalle = {'A toutes les 10 secondes':10,
                                'A toutes les minutes':60,
                                'A toutes les heures':60*60,
                                'A tous les jours':60*60*24,
                                'A toutes les semaines':60*60*24*7,
                                 'Lors dun evenement':0}
    def VerifierBD(self):
        self.ListeDeclencheur = self.ObtenirEnregistrement('DECLENCHEUR', ['TYPE', 'INTERVALLE', 'DERNIEREFOIS', 'CONTRAINTEID', 'RAPPORTNAME', 'AVERTISSEMENT', 'ID', 'UTILISATEUR'])[1]
        print "dec ", self.ListeDeclencheur
        objtimer = threading.Timer(60.0,self.VerifierBD)
        objtimer.start()
        
    def ObtenirDeclencheur(self, nomUtilisateur):
        Retour = []
        for dec in self.ListeDeclencheur:
            if dec['UTILISATEUR'] == nomUtilisateur or dec['UTILISATEUR'] == "Tous les utilisateurs":
                print "intervalle ", (time.time() - float(dec['DERNIEREFOIS']))
                if((time.time() - dec['DERNIEREFOIS'] ) > self.Dict_Invervalle[dec['INTERVALLE']]):
                   Retour.append(dec)
                   
        print "retour", Retour
        return [1,Retour]
        #return self.ObtenirEnregistrement('DECLENCHEUR', ['TYPE', 'INTERVALLE', 'DERNIEREFOIS', 'CONTRAINTEID', 'RAPPORTNAME', 'AVERTISSEMENT', 'ID', 'UTILISATEUR'])[1]
    def DemarrerConnection(self, NomConnection,MotDePasse,NomBaseDonnee):
        try:
            print NomConnection+'/'+MotDePasse+'@'+NomBaseDonnee
            self.Connection = cx_Oracle.connect(NomConnection+'/'+MotDePasse+'@'+NomBaseDonnee)
            self.Curseur = self.Connection.cursor()
            return [1, "Connection a la base de donnees reussit"]
        except cx_Oracle.DatabaseError, erreur:
            erreurNo=erreur.args[0].code
            if DictionnaireSQL.erreurDBMsg.has_key(erreurNo):
                return [0,DictionnaireSQL.erreurDBMsg[erreurNo]]
            else:
                self.Erreur=str(erreur.message)                
                return [0, self.Erreur]
            
    def Authentification(self,nom,pwd):
        requete="SELECT COUNT(*) FROM perp_utilisateurs WHERE UPPER(IDENTIFIANT)='"+\
                     nom.upper()+"' AND MOT_DE_PASSE='" +pwd+"'" 
        self.Curseur.execute(requete)
        rows=self.Curseur.fetchall()
        if (rows[0][0]>0):
            return[1,"Athentification r�sussite"]
        else:
            return[0,"Veuillez v�rifier le nom d'utilisateur ou le mot de passe"]
        pass
    def DoCommit(self):
        self.ExecuterRequeteSQL("commit")
        pass
    def ArreterConnection(self):
        self.Curseur.close()
        self.Connection.close()
        
    def ExecuterRequeteSQL(self, RequeteSQL):
        print "Execution de ", RequeteSQL
        try:
           self.Curseur.execute(RequeteSQL)
           Reponse = 'Requete' + RequeteSQL + ' executee avec succes'
           return [1, Reponse]
        except cx_Oracle.DatabaseError, erreur:
            return [0,erreur.args[0].message]

    
    def ExecuterRequeteSQLWhere(self, RequeteSQL):
        try:
           self.Curseur.execute(RequeteSQL)
           rows=self.Curseur.fetchall()
           print "reponse ", rows
#           for index1 in range(len(rows)):
#               for index2 in range(len(rows[index1])):
#                   print "row", rows[index1][index2]
#                   rows[index1][index2][0] = self.supprime_accent(rows[index1][index2][0])
           return [1, rows]
        except cx_Oracle.DatabaseError, erreur:
              return [0,erreur.args[0].message]
        
    def RecevoirVue(self,NomVue):       
        f=open('Temp/'+NomVue+'.py','r')         
        p=f.readlines()         
        f.close()         
        return p         
        pass
     
    def ObtenirListeChamps(self,table):
        RequeteSQL="SELECT COLUMN_NAME,DATA_TYPE,DATA_LENGTH,DATA_PRECISION,DATA_SCALE FROM USER_TAB_COLUMNS WHERE LOWER(TABLE_NAME)='"+table.lower()+"'"
        rows=self.ExecuterRequeteSQLWhere(RequeteSQL)
        return rows

    def ObtenirListeTables(self,user,profil):
        if (user.upper()=='ADMIN' or profil.upper()=='ADMIN'):
            RequeteSQL="SELECT TABLE_NAME FROM USER_TABLES"
        else:
            RequeteSQL="SELECT TABLE_NAME FROM USER_TABLES WHERE LOWER(TABLE_NAME) NOT IN('tablesstandards',"+\
                "'perp_contraintes','perp_profils','perp_utilisateurs','perp_serveurs','users')"
        rows=self.ExecuterRequeteSQLWhere(RequeteSQL)
        return rows
    
  
    def AlterTable(self,args,operation='ajouter'):
        RequeteSQL="ALTER TABLE "+ args['table']
        if operation.lower() in ['ajouter','modifier']:
            if operation.lower()=='ajouter':
                RequeteSQL= RequeteSQL + " ADD("
            elif operation.lower()=='modifier':
                RequeteSQL= RequeteSQL + " MODIFY("
            RequeteSQL = RequeteSQL + args['champs']+" "+ DicoSQLType.OracleType[args['type']] +")"
            if args['type'] in ['texte','numerique']:
                RequeteSQL= RequeteSQL[:-1] 
                RequeteSQL+= "("+ args['taille'] +")"
                if args['type'] in ['numerique']:
                    RequeteSQL= RequeteSQL[:-1]
                    if args['precision']!=0:
                        RequeteSQL+= ","+ args['precision'] +")"
                    else:
                        RequeteSQL+=")"
                RequeteSQL+=")"
            #elif args['type'] in ['date','caractere']:
            #    RequeteSQL+=")"
            pass
        elif operation.lower()=='supprimer':
            RequeteSQL+=" DROP("+args['champs']+")" # Drop est propre à Oracle
        rows=self.ExecuterRequeteSQL(RequeteSQL)
        return rows
    
    def CreerTable(self,args):
        RequeteSQL="CREATE TABLE "+ args['table']+ " ("         
        for champ in args['listechamps']:
            RequeteSQL+= champ['nom']+ " "+DicoSQLType.OracleType[champ['type']]
            if champ['type'] in ['texte','numerique']:
                RequeteSQL+= "("+ champ['taille'] +")"
                if (champ['type'] in ['numerique']) and (champ['precision']!=0):
                    RequeteSQL= RequeteSQL[:-1]
                    RequeteSQL+= ","+ champ['precision'] +")"
                RequeteSQL+=","
            elif champ['type'] in ['date','caractere']:
                RequeteSQL+="),"
            pass
        RequeteSQL=RequeteSQL[:-1]+")"
        rows=self.ExecuterRequeteSQL(RequeteSQL)
        return rows
    
    #retourner les enregsitrements sous forme de dictionnaires
    
    def ObtenirEnregistrement(self, table, colonnes=None, where=None):
        Requete = "select "
        for colonne in colonnes :
                Requete = Requete + colonne + " ,"
        print Requete
        Requete = Requete[:-1] + "from " + table + " "
        if where <> None:
            Requete = Requete + " " +where
        print "requete " + Requete
        Reponse =  self.ExecuterRequeteSQLWhere(Requete)
        if Reponse[0] == 0:
            return Reponse
        enregistrements = []
        for oneEnregistrement in Reponse[1]:
            enregistrement = {}
            for index in range(len(colonnes)):
               if oneEnregistrement[index] == None:
                   enregistrement[colonnes[index]] =  ""
               else:
                   enregistrement[colonnes[index]] = oneEnregistrement[index]
            enregistrements.append(enregistrement)
        return [1,enregistrements]
   
    def supprime_accent(self, ligne):
        """ supprime les accents du texte source """
        accents = { 'a': ['à', 'ã', 'á', 'â'],
                    'e': ['é', 'è', 'ê', 'ë'],
                    'i': ['î', 'ï'],
                    'u': ['ù', 'ü', 'û'],
                    'o': ['ô', 'ö'] }
        for (char, accented_chars) in accents.iteritems():
            for accented_char in accented_chars:
                ligne = ligne.replace(accented_char, char)
        return ligne

    def ObtenirContraintes(self, table):
       NomsChamps = []
       Reponse = self.ObtenirListeChamps('PERP_CONTRAINTES')
       if Reponse[0] == 0:
           return Reponse
       for Champ in Reponse[1]:
           NomsChamps.append(Champ[0])
       where = "where OPERANDE_GAUCHE = '" + table + "'"
       Reponse = self.ObtenirEnregistrement('PERP_CONTRAINTES', NomsChamps, where)
       self.DoCommit()
       return Reponse
    
    def MettreAJour(self,  table, donnees, where):
        Requete = "update " + table + " set "
        for donnee in donnees.keys():
            if donnees[donnee] <> None:
                Requete = Requete +  donnee + " = " + "'" + str(donnees[donnee]) + "'" + ", "
        Requete = Requete[:-2] + " " + where
        print "maj ", Requete
        Reponse = self.ExecuterRequeteSQL(Requete)
        self.ExecuterRequeteSQL("commit")
        return Reponse
    
    def InsererEnregistrement(self, table, donnees):
         Requete = "insert into " + table + "("
         for champ in champ.keys:
             Requete = Requete + champ + ", "
         Requete = Requete[:-2] + ") values ("  
         for key in donnees.keys:
             Requete = Requete + donnees[key] + ", "
         Requete = Requete[:-2] + ")"
         Reponse = self.ExecuterRequeteSQL(Requete) 
         self.ExecuterRequeteSQL("commit")
             
    def InsererContrainte(self, Contrainte):
         Contrainte_ID = "select PERP_CONTRAINTES_ID.NEXTVAL from dual"
         Contrainte['ID_CONTRAINTES'] = Contrainte_ID
         self.InsererEnregistrement(self, 'PERP_CONTRAINTES', Contrainte)
         
    def EffacerEnregistrement(self, table, where):
        Requete = "delete " + table + " " + where
        Reponse = self.ExecuterRequeteSQL(Requete)
        if Reponse[0] == 1:
            self.ExecuterRequeteSQL("commit")

#    update <table name> set (<col1 = val1, col2 = val2,col3 = val3,…)   
#    where <expression identifying rows to change>; 

    def EffacerEnregistrement(self, table, where=None):
        Requete = "DELETE  " + table + where
        Reponse = self.ExecuterRequeteSQL(Requete)
        self.DoCommit()
        return Reponse
    def InsererEnregistrement(self, table, donnees):
        Requete = "INSERT INTO "+table+ "("
        for key in donnees.keys():
           Requete = Requete + key+ ", "
        Requete = Requete[:-2] 
        Requete = Requete+ ") values ("
        for key in donnees.keys():
            print donnees
            Requete = Requete + "'" + donnees[key] +"'" + ", "
        Requete= Requete[:-2]+") "
        Reponse = self.ExecuterRequeteSQL(Requete)
        self.DoCommit()
        return Reponse
        
        
    
class DicoSQLType:
        OracleType={'texte':'VARCHAR2',
                    'numerique':'NUMBER',
                    'date':'DATE',
                    'caractere':'CHAR'}
                
class DictionnaireSQL(object):
    
       erreurSqlMsg={
           0:'Oups!..Erreur indeterminee',
           # "select * from pme"
           942:'Table ou vue inexistante',
           #"select 2 / 0 from dual"
           1476:'le diviseur est egal a zero',
           # "select N from emp"
           904:'identificateur non valide',
           # "slect Nome from emp"
           # "--rem"
           900:'instruction SQL non valide', 
           #"select nome rom emp"
           923:'mot-cle FROM abscent a l\'emplacement prevu',
           # "select Nom from emp where" 
           936:'expression absente',
           # "select Nome from emp where nome='roy' orde"
           933:'la commande SQL ne se termine pas correctement',
           #"select Nome from emp where nome='e' order"
           924:'mot-cle BY absent',
           # "select Nome from emp where nome='e' by nome having"
           921:'fin inattendue d\'une commande SQL',
           # "Alter emp"
           940: 'commande ALTER non valide',
           # "create table"
           901: 'commande CREATE non valide',
           #"insert"
           925: 'mot-cle INTO absent',
           # "update emp
           971: 'mot-cle SET absent',
           #"alter table emp delete"
           1735: 'option ALTER TABLE non valide',
           # ""
           24373: 'longueur indiquee non valide pour l\'instruction',
           # "set"
           922: 'option erronee ou absente',
           955: 'cette table existe deja'}

           # Erreur de la base de donn�es elle m�me
       erreurDBMsg={#con=cx_Oracle.Connection('dalek/*@titan')
                         0:'Oups!..Erreur indeterminee',
                         1005:'aucun mot de passe indique',
                         1017:'Nom d\'utilisateur ou mot de passe invalid',
                         28000:'Le compte est verouille',
                         28009:'La connection en tant que SYS devrait etre SYSDBA ou SYSOPER',
                         12154: 'TNS : l\'identificateur de connexion indique n\'a pas pu etre resolu'}
